#!/usr/bin/python
# -*- coding:utf-8 -*-

# Import required python libraries
import datetime
import os
import subprocess
import time
import logging
import pymysql

# Set log format
logging.basicConfig(format='%(asctime)s - %(pathname)s[line:%(lineno)d] - %(levelname)s:%(message)s',
                    level=logging.INFO)

# MySQL database details to which backup to be done. Make sure below user having enough privileges to take databases backup.
# 数据库主机地址
MYSQL_HOST = 'XXX.XXX.XXX.XXX'
# MySQL数据库用户名
MYSQL_USERNAME = 'XXX'
# 数据库端口
MYSQL_PORT = 3306
# 数据库密码
MYQSL_PASSWORD = 'XXXXXXXXX'
# 需要备份的数据库名称
# DB_NAME = 'main'
# 排除，不进行备份操作的数据库名称集合
DISABLED_DATABASES = {'information_schema', 'mysql', 'performance_schema', 'sys'}
# 备份文件存放路径
BACKUP_PATH = '/backup/mysql/'
# Getting current datetime to create seprate backup folder.
DATETIME = datetime.datetime.now().strftime("%Y%m%d%H%M%S")
TODAYBACKUPPATH = BACKUP_PATH + MYSQL_HOST + '/' + DATETIME
# 备份保留时长
DAY = 3


def mkdir_if_not_exists(path):
    """
    判断备份目录是否存在，不存在则创建
    Args:
        path:带创建目录名称
    """
    if not os.path.exists(path):
        os.makedirs(path)
    logging.info("creating backup folder")


def create_mysql_conn(db="mysql"):
    """
    创建并返回一个MySQL数据库连接
    Args:
        db:要连接的数据库名称
    """
    conn = pymysql.connect(host=MYSQL_HOST, port=MYSQL_PORT, user=MYSQL_USERNAME, password=MYQSL_PASSWORD, db='mysql')
    return conn


def read_all_databases():
    """
    从数据库中读取全部数据库名称
    :return:
    数据库名称list
    """
    logging.info('读取全部数据库名称')
    conn = create_mysql_conn()
    cursor = conn.cursor()
    # 查询服务器上有哪些数据库
    cursor.execute('show databases')
    res = cursor.fetchall()
    databases = {item[0] for item in res}
    # 排除掉指定不备份的数据库
    databases = list(databases - DISABLED_DATABASES)
    cursor.close()
    conn.close()
    logging.info('读取数据库完毕，数据库列表如下：{}'.format(databases))
    return databases


def backup_database(database):
    """
    备份指定数据库的数据和结构
    Args:
        database: 待备份的数据库名称
    """
    logging.info('开始备份数据库{}...'.format(database))
    # 通过调用mysqldump完成指定数据库的备份
    command = 'mysqldump -h%s -u%s -p%s %s | gzip > %s/%s.sql.gz' % (
        MYSQL_HOST, MYSQL_USERNAME, MYQSL_PASSWORD, database, TODAYBACKUPPATH, database)
    exit_code = subprocess.call(command, shell=True)
    # 判断命令是否正常执行，异常则直接抛出
    if exit_code != 0:
        raise Exception('在备份数据库的过程中出错，请检查！')
    logging.info('数据库 {} 备份完毕！'.format(database))


def del_old_backup(path):
    """
    删除保留时长以外的数据备份
    """
    old_folder = subprocess.Popen("find {}  -maxdepth 3  -mindepth 2 -mtime +{} -type d".format(path, DAY),stdout=subprocess.PIPE, stderr=subprocess.STDOUT, shell=True).communicate()
    logging.info('时间戳为{}的备份将被删除'.format(old_folder))
    str_old_folder = old_folder[0].decode("utf-8", errors="ignore")
    logging.info("{}".format(str_old_folder))
    list_lid_folder = str_old_folder.split('\n')
    for folder in list_lid_folder:
        if folder.strip():
            logging.info("删除{}".format(folder))
            os.system("rm -rf {}".format(folder))


def backup():
    """
    读取全部数据库名称，并对这些数据库的数据和结构进行备份
    """
    # 检查备份路径是否存在，不存在则进行创建
    mkdir_if_not_exists(TODAYBACKUPPATH)
    # 读取全部待备份数据库名称
    databases = read_all_databases()
    # 逐个对数据库进行备份
    for database in databases:
        backup_database(database)
    # 删除旧备份
    del_old_backup(BACKUP_PATH)


if __name__ == '__main__':
    backup()